package service;

import DAO.OrderDaoImpl;
import beans.Order;
import beans.User;
import beans.Warehouse;
import dbutils.DBHelper;
import utils.DateUtil;
import utils.ValidationUtil;

import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;

import static dbutils.DBHelper.*;

public class MerchantService {

    // 检查用户名是否存在
    public static boolean isUsernameExists(String merchant) {
        Connection conn = DBHelper.getConn();
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            String checkNameSql = "SELECT * FROM merchant WHERE mname = ?";
            stat = conn.prepareStatement(checkNameSql);
            stat.setString(1, merchant);
            rs = stat.executeQuery();
            return rs.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, stat, rs);
        }
        return false;
    }

    // 检查手机号是否存在
    public static boolean isPhoneExists(String mphone) {
        Connection conn = DBHelper.getConn();
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            String checkPhoneSql = "SELECT * FROM merchant WHERE mphone = ?";
            stat = conn.prepareStatement(checkPhoneSql);
            stat.setString(1, mphone);
            rs = stat.executeQuery();
            return rs.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, stat, rs);
        }
        return false;
    }

    /*
     * 插入空账号，用于注册
     */
    public void createNewMerchant(User merchant) {
        Connection conn = DBHelper.getConn();
        String sql = "INSERT merchant (mname,mphone,mpassword,mmoney) VALUE(?,?,?,?);";
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1, merchant.getName());
            stat.setString(2, merchant.getPhone());
            stat.setString(3, merchant.getPassword());
            stat.setString(4, String.valueOf(merchant.getMoney()));
            stat.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, stat, rs);
        }
    }


    /*
     * 注册功能
     */
    public void signIn(String name, String phone, String password, String confirmPassword) {
        User merchant = new User();
        String merchantName = name;
        String merchantPhone = phone;
        String merchantPassword = ValidationUtil.getPassword(password, confirmPassword);
        merchant.setName(merchantName);
        merchant.setPhone(merchantPhone);
        merchant.setPassword(merchantPassword);
        merchant.setMoney(0);
        //新建用户
        createNewMerchant(merchant);

        // 更新用户到数据库
        updateMerchant(merchant);

        JOptionPane.showMessageDialog(null, "用户：" + merchantName + " 注册完成！。");

        Date time = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss");
        String dateTimeNow = sdf.format(time);
        LogService.addLogs(merchant, "商家 " + merchantName + " 于" + dateTimeNow + "注册系统。");
    }

    //用户名登录
    public User loginN(String name, String password) {
        User merchant = findMname(name);
        // 判断账户是否存在，密码是否匹配
        if (merchant != null && merchant.getPassword().equals(password)) {
            // 登录成功
            JOptionPane.showMessageDialog(null, "登录成功！当前账号为：" + name);
            // 记录登录日志
            LogService.addLogs(merchant, "商家 " + merchant.getName() + "登录系统");
            return merchant;
        } else {
            // 登录失败，密码错误
            JOptionPane.showMessageDialog(null, "账号或密码错误，请重新输入");
            return null;
        }
    }

    //手机号登录
    public User loginP(String phone, String password) {
        User merchant = findMphone(phone);
        // 判断账户是否存在，密码是否匹配
        if (merchant != null && merchant.getPassword().equals(password)) {
            // 登录成功
            JOptionPane.showMessageDialog(null, "登录成功！当前用户为：" + merchant.getName());
            // 记录登录日志
            LogService.addLogs(merchant, "商家 " + merchant.getName() + "登录系统");
            return merchant;
        } else {
            // 登录失败，密码错误
            JOptionPane.showMessageDialog(null, "账号或密码错误，请重新输入");
            return null;
        }

        //跳转到顾客界面


    }


    /*
     * 根据name查找数据库的账号
     * @param name
     * @return 返回对应name的 merchant 对象，如果不存在返回null
     */
    public User findMname(String mname) {
        if (mname == null) {
            return null;
        }
        Connection conn = DBHelper.getConn();
        String sql = "select * from merchant WHERE mname = ? ; ";
        PreparedStatement stat = null;
        ResultSet rs = null;
        User merchant = new User();
        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1, mname);
            rs = stat.executeQuery();//executeQuery 该方法用来执行查询语句
            while (rs.next()) {
                merchant.setId(rs.getInt("mid"));
                merchant.setName(rs.getString("mname"));
                merchant.setPhone(rs.getString("mphone"));
                merchant.setPassword(rs.getString("mpassword"));
                merchant.setMoney(rs.getDouble("mmoney"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, stat, rs);
        }
        if (merchant.getId() == 0) {
            return null;
        } else {
            return merchant;
        }
    }

    /*
     * 根据phone查找数据库的账号
     * @param phone
     * @return 返回对应phone的 merchant 对象，如果不存在返回null
     */
    public User findMphone(String cname) {
        if (cname == null) {
            return null;
        }
        Connection conn = DBHelper.getConn();
        String sql = "select * from merchant WHERE mphone = ? ; ";
        PreparedStatement stat = null;
        ResultSet rs = null;
        User merchant = new User();
        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1, cname);
            rs = stat.executeQuery();//executeQuery 该方法用来执行查询语句
            while (rs.next()) {
                merchant.setId(rs.getInt("mid"));
                merchant.setName(rs.getString("mname"));
                merchant.setPhone(rs.getString("mphone"));
                merchant.setPassword(rs.getString("mpassword"));
                merchant.setMoney(rs.getDouble("mmoney"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, stat, rs);
        }
        if (merchant.getId() == 0) {
            return null;
        } else {
            return merchant;
        }
    }


    /*
     * merchant
     * @param merchant 已设定好数据的 merchant 对象
     */
    public void updateMerchant(User merchant) {
        Connection conn = DBHelper.getConn();
        String sql = "UPDATE merchant SET mname = ?,mphone = ?,mpassword = ?,mmoney = ? WHERE mid = ?";
        PreparedStatement stat = null;
        ResultSet rs = null;

        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1, merchant.getName());
            stat.setString(2, merchant.getPhone());
            stat.setString(3, merchant.getPassword());
            stat.setDouble(4, merchant.getMoney());
            stat.setInt(5, merchant.getId());
            stat.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, stat, rs);
        }
    }



    //修改密码
    public void updatePassword(User merchant, String oldPassword, String newPassword) {
        // 首先进行参数有效性检查
        if (newPassword.isEmpty()) {
            JOptionPane.showMessageDialog(null, "新密码不能为空！", "错误", JOptionPane.ERROR_MESSAGE);
            return;
        }

        if (oldPassword.equals(newPassword)) {
            JOptionPane.showMessageDialog(null, "新密码不能和旧密码相同！", "错误", JOptionPane.ERROR_MESSAGE);
            return;
        }

        Connection conn = DBHelper.getConn();
        String sql = "SELECT * FROM merchant WHERE mid = ? AND mpassword = ?";
        PreparedStatement selectStat = null;
        String updateSql = "UPDATE merchant SET mpassword = ? WHERE mid = ?";
        PreparedStatement updateStat = null;

        try {
            // 验证旧密码是否正确
            selectStat = conn.prepareStatement(sql);
            selectStat.setInt(1, merchant.getId());
            selectStat.setString(2, oldPassword);
            ResultSet resultSet = selectStat.executeQuery();

            if (resultSet.next()) {
                // 更新密码
                updateStat = conn.prepareStatement(updateSql);
                updateStat.setString(1, newPassword);
                updateStat.setInt(2, merchant.getId());
                int rowsAffected = updateStat.executeUpdate();

                if (rowsAffected > 0) {
                    JOptionPane.showMessageDialog(null, "密码修改成功！");
                    LogService.addLogs(merchant,"商家 " + merchant.getName() + " 修改了自己的密码");
                } else {
                    JOptionPane.showMessageDialog(null, "密码修改失败！", "错误", JOptionPane.ERROR_MESSAGE);
                }
            } else {
                JOptionPane.showMessageDialog(null, "旧密码错误！", "错误", JOptionPane.ERROR_MESSAGE);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, selectStat, null);
            DBHelper.closeAll(conn, updateStat, null);
        }
    }

    // 充值
    public void rechargeBalance(User merchant, double amount) {
        Connection conn = DBHelper.getConn();
        String sql = "UPDATE merchant SET mmoney = mmoney + ? WHERE mid = ?";
        PreparedStatement stat = null;

        try {
            stat = conn.prepareStatement(sql);
            stat.setDouble(1, amount);
            stat.setInt(2, merchant.getId());
            int rowsAffected = stat.executeUpdate();

            if (rowsAffected > 0) {
                JOptionPane.showMessageDialog(null, "充值成功！", "提示", JOptionPane.INFORMATION_MESSAGE);
                LogService.addLogs(merchant,"商家 " + merchant.getName() + " 充值金额：" + amount + "成功");
            } else {
                JOptionPane.showMessageDialog(null, "充值失败！", "提示", JOptionPane.ERROR_MESSAGE);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, stat, null);
        }
    }


    // 商品展示
    public JTable createJTable(User merchant) {
        // 创建表头
        String[] columnNames = {"ID", "商品", "数量", "价格", "尺码", "商家", "生产商", "状态"};

        // 创建 JTable 对象
        JTable table = new JTable();

        // 设置表头
        DefaultTableModel tableModel = (DefaultTableModel) table.getModel();
        tableModel.setColumnIdentifiers(columnNames);

        // 查询数据库数据并添加到表格中
        Connection conn = DBHelper.getConn();
        String sql = "SELECT * FROM shoe WHERE smerchant=?";
        PreparedStatement selectStat = null;
        ResultSet resultSet = null;
        try {
            selectStat = conn.prepareStatement(sql);
            selectStat.setString(1, merchant.getName());
            resultSet = selectStat.executeQuery();

            while (resultSet.next()) {
                int id = resultSet.getInt("sid");
                String name = resultSet.getString("sname");
                int quantity = resultSet.getInt("scount");
                double price = resultSet.getDouble("sprice");
                String size = resultSet.getString("ssize");
                String smerchant = resultSet.getString("smerchant");
                String producer = resultSet.getString("sproducer");
                String status = resultSet.getString("sstatus");

                // 添加数据行
                Object[] rowData = {id, name, quantity, price, size, smerchant, producer, status};
                tableModel.addRow(rowData);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, selectStat, resultSet);
        }

        return table;
    }


    // 修改商品价格
    public void performModifyPrice(JTable table,User merchant) {
        int selectedRow = table.getSelectedRow();
        if (selectedRow == -1) {
            JOptionPane.showMessageDialog(null, "请先选择一行商品！", "提示", JOptionPane.INFORMATION_MESSAGE);
            return;
        }

        int id = (int) table.getValueAt(selectedRow, table.getColumn("ID").getModelIndex());

        // 显示输入对话框，获取新的商品价格
        double newPrice;

        String inputPrice = JOptionPane.showInputDialog(null, "请输入新的价格：", "修改商品价格", JOptionPane.PLAIN_MESSAGE);
        if (inputPrice == null) {
            return; // 用户点击了取消按钮
        } else {
            try {
                newPrice = Double.parseDouble(inputPrice);
            } catch (NumberFormatException e) {
                JOptionPane.showMessageDialog(null, "请输入有效的价格！", "错误", JOptionPane.ERROR_MESSAGE);
                return;
            }
        }

        DefaultTableModel model = (DefaultTableModel) table.getModel();
        model.setValueAt(newPrice, selectedRow, table.getColumn("价格").getModelIndex());
        String shoe = (String)table.getValueAt(selectedRow, table.getColumn("商品").getModelIndex());
        LogService.addLogs(merchant,"商家 " + merchant.getName() + " 修改了商品 " + shoe + "的价格为：" + newPrice);

        // 更新数据库中的商品价格
        updatePriceInDatabase(id, newPrice,merchant);
    }


    // 修改商品状态
    public void performModifyStatus(User merchant,JTable table) {
        int selectedRow = table.getSelectedRow();
        if (selectedRow == -1) {
            JOptionPane.showMessageDialog(null, "请先选择一行商品！", "提示", JOptionPane.INFORMATION_MESSAGE);
            return;
        }

        int id = (int) table.getValueAt(selectedRow, table.getColumn("ID").getModelIndex());

        // 显示选择对话框，获取新的商品状态
        Object[] statusOptions = {"已上架", "已下架"};
        int statusChoice = JOptionPane.showOptionDialog(null, "请选择新的状态：", "修改商品状态", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE, null, statusOptions, statusOptions[0]);
        if (statusChoice == JOptionPane.CLOSED_OPTION) {
            return; // 用户点击了关闭按钮
        } else {
            String newStatus = statusOptions[statusChoice].toString();
            DefaultTableModel model = (DefaultTableModel) table.getModel();
            model.setValueAt(newStatus, selectedRow, table.getColumn("状态").getModelIndex());
            String shoe = (String)table.getValueAt(selectedRow, table.getColumn("商品").getModelIndex());
            LogService.addLogs(merchant,"商家 " + " 修改了商品 " + shoe + " 的状态为：" + newStatus);
            // 更新数据库中的商品状态
            updateProductStatusInDatabase(id, newStatus);
        }
    }


    // 更新商品价格到数据库
    public void updatePriceInDatabase(int id, double newPrice,User merchant) {
        // 获取数据库连接
        Connection conn = DBHelper.getConn();
        PreparedStatement stmt = null;

        try {

            // 创建预处理语句
            String sql = "UPDATE shoe SET sprice=? WHERE sid=?";
            stmt = conn.prepareStatement(sql);
            stmt.setDouble(1, newPrice);
            stmt.setInt(2, id);

            // 执行更新操作
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接和语句对象
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    // 更新商品状态到数据库
    public void updateProductStatusInDatabase(int id, String newStatus) {
        // 获取数据库连接
        Connection conn = DBHelper.getConn();
        PreparedStatement stmt = null;

        try {

            // 创建预处理语句
            String sql = "UPDATE shoe SET sstatus=? WHERE sid=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, newStatus);
            stmt.setInt(2, id);

            // 执行更新操作
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接和语句对象
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    //添加商品
    public void performAddProduct(JTable table, User merchant) {
        String name = JOptionPane.showInputDialog(null, "请输入商品名称：", "添加商品", JOptionPane.PLAIN_MESSAGE);
        if (name == null || name.isEmpty()) {
            return; // 用户点击了取消按钮或者没有输入商品名称
        }

        // 检查商家是否有该商品
        boolean hasProduct = checkIfMerchantHasProduct(name, merchant);
        if (!hasProduct) {
            JOptionPane.showMessageDialog(null, "商家没有该商品，请先添加商品信息！", "错误", JOptionPane.ERROR_MESSAGE);
            return;
        }

        // 输入商品数量
        int quantity;
        String inputQuantity = JOptionPane.showInputDialog(null, "请输入商品数量：", "添加商品", JOptionPane.PLAIN_MESSAGE);
        if (inputQuantity == null) {
            return; // 用户点击了取消按钮
        } else {
            try {
                quantity = Integer.parseInt(inputQuantity);
            } catch (NumberFormatException e) {
                JOptionPane.showMessageDialog(null, "请输入有效的商品数量！", "错误", JOptionPane.ERROR_MESSAGE);
                return;
            }
        }

        // 检查库存是否足够
        boolean hasEnoughStock = checkIfEnoughStock(name, quantity, merchant);
        if (!hasEnoughStock) {
            JOptionPane.showMessageDialog(null, "库存不足，请减少商品数量！", "错误", JOptionPane.ERROR_MESSAGE);
            return;
        }

        // 添加商品到表格和数据库
        DefaultTableModel model = (DefaultTableModel) table.getModel();
        model.addRow(new Object[]{name, quantity});

        // 更新仓库库存数量
        updateWarehouseStock(name, quantity, merchant);
        LogService.addLogs(merchant,"商家 " + merchant.getName() + " 添加了新商品：" + name);
    }


    //检查商家仓库中是否有这件商品
    public boolean checkIfMerchantHasProduct(String name, User merchant) {
        // 获取数据库连接
        Connection conn = DBHelper.getConn();
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            // 创建预处理语句
            String sql = "SELECT wname FROM warehouse WHERE wmerchant=? AND wname=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, merchant.getName());
            stmt.setString(2, name);

            // 执行查询操作
            rs = stmt.executeQuery();

            // 判断查询结果是否存在该商品
            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接、语句对象和结果集
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return false;
    }


    //检查仓库中是否有足够的库存
    public boolean checkIfEnoughStock(String name, int quantity, User merchant) {
        // 获取数据库连接
        Connection conn = DBHelper.getConn();
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            // 创建预处理语句
            String sql = "SELECT wcount FROM warehouse WHERE wmerchant=? AND wname=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, merchant.getName());
            stmt.setString(2, name);

            // 执行查询操作
            rs = stmt.executeQuery();

            // 判断查询结果中的库存数量是否足够
            if (rs.next()) {
                int stock = rs.getInt("wcount");
                if (quantity <= stock) {
                    return true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接、语句对象和结果集
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return false;
    }


    //更新数据到仓库的数据库
    public void updateWarehouseStock(String name, int quantity, User merchant) {
        // 获取数据库连接
        Connection conn = DBHelper.getConn();
        PreparedStatement stmt = null;

        try {
            // 创建预处理语句
            String sql = "UPDATE warehouse SET wcount=wcount-? WHERE wmerchant=? AND wname=?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, quantity);
            stmt.setString(2, merchant.getName());
            stmt.setString(3, name);

            // 执行更新操作
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接和语句对象
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    // 补货
    public void performReplenishProduct(JTable table, User merchant) {
        int selectedRow = table.getSelectedRow();

        if (selectedRow == -1) {
            JOptionPane.showMessageDialog(null, "请先选择要补货的商品！", "错误", JOptionPane.ERROR_MESSAGE);
            return;
        }

        String name = (String) table.getValueAt(selectedRow, table.getColumn("商品").getModelIndex());

        // 输入补货数量
        int replenishQuantity;
        String inputReplenishQuantity = JOptionPane.showInputDialog(null, "请输入补货数量：", "补货商品", JOptionPane.PLAIN_MESSAGE);
        if (inputReplenishQuantity == null) {
            return; // 用户点击了取消按钮
        } else {
            try {
                replenishQuantity = Integer.parseInt(inputReplenishQuantity);
            } catch (NumberFormatException e) {
                JOptionPane.showMessageDialog(null, "请输入有效的补货数量！", "错误", JOptionPane.ERROR_MESSAGE);
                return;
            }
        }

        // 判断仓库中是否存在该商品，并且库存数量是否足够
        Warehouse warehouse = getWarehouseByMerchantAndProductName(merchant, name);
        if (warehouse == null) {
            JOptionPane.showMessageDialog(null, "仓库中不存在该商品，请确认是否添加到仓库中！", "错误", JOptionPane.ERROR_MESSAGE);
            return;
        }
        if (replenishQuantity > warehouse.getCount()) {
            JOptionPane.showMessageDialog(null, "补货数量超过仓库库存，无法完成补货！", "错误", JOptionPane.ERROR_MESSAGE);
            return;
        }

        // 更新仓库库存数量
        updateWarehouseCountForReplenish(warehouse.getId(), replenishQuantity);

        // 更新商品库存数量
        updateShoeCountForReplenish(name, replenishQuantity);

        int count = (int) table.getValueAt(selectedRow, table.getColumn("数量").getModelIndex());

        int newcount = replenishQuantity + count;
        DefaultTableModel model = (DefaultTableModel) table.getModel();
        model.setValueAt(newcount, selectedRow, table.getColumn("数量").getModelIndex());

        JOptionPane.showMessageDialog(null, "补货成功！", "提示", JOptionPane.INFORMATION_MESSAGE);
        LogService.addLogs(merchant,"商家 " + merchant.getName() + " 补货 " + name + " 数量为：" + newcount);
    }


    // 根据商家和商品名称查询仓库中的商品信息
    public Warehouse getWarehouseByMerchantAndProductName(User merchant, String productName) {
        // 获取数据库连接
        Connection conn = DBHelper.getConn();
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            // 创建预处理语句
            String sql = "SELECT * FROM warehouse WHERE wmerchant=? AND wname=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, merchant.getName());
            stmt.setString(2, productName);

            // 执行查询操作
            rs = stmt.executeQuery();

            // 判断查询结果是否存在该商品
            if (rs.next()) {
                // 构造Warehouse对象返回
                Warehouse warehouse = new Warehouse();
                warehouse.setId(rs.getInt("wid"));
                warehouse.setMerchant(merchant);
                warehouse.setProductName(rs.getString("wname"));
                warehouse.setCount(rs.getInt("wcount"));
                return warehouse;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接、语句对象和结果集
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }


    // 更新仓库库存数量
    public void updateWarehouseCountForReplenish(int id, int replenishQuantity) {
        // 获取数据库连接
        Connection conn = DBHelper.getConn();
        PreparedStatement stmt = null;

        try {
            // 创建预处理语句
            String sql = "UPDATE warehouse SET wcount=wcount-? WHERE wid=?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, replenishQuantity);
            stmt.setInt(2, id);

            // 执行更新操作
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接和语句对象
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    // 更新商品库存数量
    public void updateShoeCountForReplenish(String name, int replenishQuantity) {
        // 获取数据库连接
        Connection conn = DBHelper.getConn();
        PreparedStatement stmt = null;

        try {
            // 创建预处理语句
            String sql = "UPDATE shoe SET scount=scount+? WHERE sname=?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, replenishQuantity);
            stmt.setString(2, name);

            // 执行更新操作
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接和语句对象
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    // 刷新商品并自动下架数量小于等于3的商品
    public void refreshProductData(JTable table, User merchant) {
        DefaultTableModel model = (DefaultTableModel) table.getModel();
        model.setRowCount(0);  // 清空表格

        try (Connection conn = DriverManager.getConnection(URL, DBUser, DBPassword);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM shoe where smerchant = '" + merchant.getName() + "';")) {

            while (rs.next()) {
                Object[] rowData = new Object[8];
                rowData[0] = rs.getInt("sid");
                rowData[1] = rs.getString("sname");
                rowData[2] = rs.getInt("scount");
                rowData[3] = rs.getDouble("sprice");
                rowData[4] = rs.getString("ssize");
                rowData[5] = rs.getString("smerchant");
                rowData[6] = rs.getString("sproducer");

                int scount = rs.getInt("scount");
                String sstatus = rs.getString("sstatus");

                // 检查是否需要自动下架
                if (scount <= 3 && sstatus.equals("已上架")) {
                    sstatus = "已下架";
                    updateStatusInDatabase(rs.getInt("sid"), sstatus); // 更新数据库中的状态
                }

                rowData[7] = sstatus;

                model.addRow(rowData);
            }

            // 刷新表格
            model.fireTableDataChanged();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }


    // 更新商品状态到数据库
    private void updateStatusInDatabase(int sid, String sstatus) {
        try (Connection conn = DriverManager.getConnection(URL, DBUser, DBPassword);
             PreparedStatement stmt = conn.prepareStatement("UPDATE shoe SET sstatus = ? WHERE sid = ?")) {

            stmt.setString(1, sstatus);
            stmt.setInt(2, sid);

            stmt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }


    //查询商品
    public void searchTable(JTable table, String keyword) {
        // 清空表格数据
        DefaultTableModel tableModel = (DefaultTableModel) table.getModel();
        tableModel.setRowCount(0);

        // 查询数据库数据并添加到表格中
        Connection conn = DBHelper.getConn();
        String sql = "SELECT * FROM shoe WHERE sname LIKE ?";
        PreparedStatement selectStat = null;
        ResultSet resultSet = null;

        try {
            selectStat = conn.prepareStatement(sql);
            selectStat.setString(1, "%" + keyword + "%");
            resultSet = selectStat.executeQuery();

            while (resultSet.next()) {
                Object[] rowData = new Object[8];
                rowData[0] = resultSet.getInt("sid");
                rowData[1] = resultSet.getString("sname");
                rowData[2] = resultSet.getInt("scount");
                rowData[3] = resultSet.getDouble("sprice");
                rowData[4] = resultSet.getString("ssize");
                rowData[5] = resultSet.getString("smerchant");
                rowData[6] = resultSet.getString("sproducer");
                rowData[7] = resultSet.getString("sstatus");

                // 添加数据行
                tableModel.addRow(rowData);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, selectStat, resultSet);
        }
    }


    //创建仓库面板
    public JTable createWJTable(User merchant) {
        // 创建表头
        String[] columnNames = {"ID", "商品", "数量", "价格", "尺码", "商家", "生产商"};

        // 创建 JTable 对象
        JTable table = new JTable();

        // 设置表头
        DefaultTableModel tableModel = (DefaultTableModel) table.getModel();
        tableModel.setColumnIdentifiers(columnNames);

        // 查询数据库数据并添加到表格中
        Connection conn = DBHelper.getConn();
        String sql = "SELECT * FROM warehouse WHERE wmerchant=?";
        PreparedStatement selectStat = null;
        ResultSet resultSet = null;
        try {
            selectStat = conn.prepareStatement(sql);
            selectStat.setString(1, merchant.getName());
            resultSet = selectStat.executeQuery();

            while (resultSet.next()) {
                int id = resultSet.getInt("wid");
                String name = resultSet.getString("wname");
                int quantity = resultSet.getInt("wcount");
                double price = resultSet.getDouble("wprice");
                String size = resultSet.getString("wsize");
                String smerchant = resultSet.getString("wmerchant");
                String producer = resultSet.getString("wproducer");

                // 添加数据行
                Object[] rowData = {id, name, quantity, price, size, smerchant, producer};
                tableModel.addRow(rowData);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, selectStat, resultSet);
        }

        return table;
    }


    // 缺货
    public  void addReplenishmentService(JTable table, User merchant) {
        int selectedRow = table.getSelectedRow();
        if (selectedRow == -1) {
            JOptionPane.showMessageDialog(null, "请先选择一行商品！", "提示", JOptionPane.INFORMATION_MESSAGE);
            return;
        }

        //判断商家余额够不够50件该商品的总价
        double balance = merchant.getMoney();
        double price = (double) table.getValueAt(selectedRow, table.getColumn("价格").getModelIndex());
        if (balance < price * 50) {
            JOptionPane.showMessageDialog(null, "商家余额不足，请充值以继续购买！", "错误", JOptionPane.ERROR_MESSAGE);
            return;
        }

        //更新商家余额
        balance = balance - price * 50;
        merchant.setMoney(balance);
        updateMerchant(merchant);


        //添加到订单里面
        String name = table.getValueAt(selectedRow, table.getColumn("商品").getModelIndex()).toString();
        String wmerchant = table.getValueAt(selectedRow, table.getColumn("商家").getModelIndex()).toString();
        String producer = table.getValueAt(selectedRow, table.getColumn("生产商").getModelIndex()).toString();
        new OrderService().addcReplenishmentService(merchant, name, String.valueOf(price), wmerchant, producer);
        JOptionPane.showMessageDialog(null, "缺货通知商家成功！", "提示", JOptionPane.INFORMATION_MESSAGE);
        LogService.addLogs(merchant,"商家 " + merchant.getName() + " 发送了缺货 " + name + "给生产商 " +producer);
    }




    //刷新仓库
    public void refreshWareData(JTable table, User merchant) {
        DefaultTableModel model = (DefaultTableModel) table.getModel();
        model.setRowCount(0);  // 清空表格

        try (Connection conn = DriverManager.getConnection(URL, DBUser, DBPassword);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM warehouse where wmerchant = '" + merchant.getName() + "';")) {

            while (rs.next()) {
                Object[] rowData = new Object[7];
                rowData[0] = rs.getInt("wid");
                rowData[1] = rs.getString("wname");
                rowData[2] = rs.getInt("wcount");
                rowData[3] = rs.getDouble("wprice");
                rowData[4] = rs.getString("wsize");
                rowData[5] = rs.getString("wmerchant");
                rowData[6] = rs.getString("wproducer");

                model.addRow(rowData);
            }

            // 刷新表格
            model.fireTableDataChanged();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }



    //仓库商品查询
    //查询商品
    public void searchWareTable(JTable table, String keyword) {
        // 清空表格数据
        DefaultTableModel tableModel = (DefaultTableModel) table.getModel();
        tableModel.setRowCount(0);

        // 查询数据库数据并添加到表格中
        Connection conn = DBHelper.getConn();
        String sql = "SELECT * FROM warehouse WHERE wname LIKE ?";
        PreparedStatement selectStat = null;
        ResultSet resultSet = null;

        try {
            selectStat = conn.prepareStatement(sql);
            selectStat.setString(1, "%" + keyword + "%");
            resultSet = selectStat.executeQuery();

            while (resultSet.next()) {
                Object[] rowData = new Object[7];
                rowData[0] = resultSet.getInt("wid");
                rowData[1] = resultSet.getString("wname");
                rowData[2] = resultSet.getInt("wcount");
                rowData[3] = resultSet.getDouble("wprice");
                rowData[4] = resultSet.getString("wsize");
                rowData[5] = resultSet.getString("wmerchant");
                rowData[6] = resultSet.getString("wproducer");

                // 添加数据行
                tableModel.addRow(rowData);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, selectStat, resultSet);
        }
    }



    //查询订单
    public void showOrderData(JTable table, User merchant) {
        DefaultTableModel model = (DefaultTableModel) table.getModel();
        model.setRowCount(0);  // 清空表格

        try (Connection conn = DriverManager.getConnection(URL, DBUser, DBPassword);
             Statement stmt = conn.createStatement();
             PreparedStatement ps = null;
             ResultSet rs = stmt.executeQuery("SELECT * FROM corders where merchant = '" + merchant.getName() + "';")) {

            while (rs.next()) {
                Object[] rowData = new Object[7];
                rowData[0] = rs.getString("orderNum");
                rowData[1] = rs.getString("user");
                rowData[2] = rs.getString("times");
                rowData[3] = rs.getString("name");
                rowData[4] = rs.getString("price");
                rowData[5] = rs.getString("merchant");
                rowData[6] = rs.getString("producer");

                model.addRow(rowData);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }


    // 刷新订单
    public JTable refreshOrderData(JTable table,User merchant) {
        // 创建表头
        String[] columnNames = {"订单编号", "用户", "下单时间", "商品名称", "商品价格", "商家", "生产商" };

        // 设置表头
        DefaultTableModel tableModel = (DefaultTableModel) table.getModel();
        tableModel.setColumnIdentifiers(columnNames);

        // 查询数据库数据并添加到表格中
        Connection conn = DBHelper.getConn();
        String sql = "SELECT * FROM corders where merchant = ?";
        PreparedStatement selectStat = null;
        ResultSet resultSet = null;

        try {
            selectStat = conn.prepareStatement(sql);
            selectStat.setString(1, merchant.getName());
            resultSet = selectStat.executeQuery();

            while (resultSet.next()) {
                Object[] rowData = new Object[7];
                rowData[0] = resultSet.getInt("orderNum");
                rowData[1] = resultSet.getString("user");
                rowData[2] = resultSet.getString("times");
                rowData[3] = resultSet.getString("name");
                rowData[4] = resultSet.getString("price");
                rowData[5] = resultSet.getString("merchant");
                rowData[6] = resultSet.getString("producer");

                // 添加数据行
                tableModel.addRow(rowData);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, selectStat, resultSet);
        }

        return table;
    }


    //顾客退货
    public void ReturnService(JTable table,User merchant){
        int selectedRow = table.getSelectedRow();
        if (selectedRow == -1) {
            JOptionPane.showMessageDialog(null, "请先选择一行商品！", "提示", JOptionPane.INFORMATION_MESSAGE);
            return;
        }

        // 获取选中行的订单编号和商家名称
        String orderNum = (String) table.getValueAt(selectedRow, table.getColumn("订单编号").getModelIndex());
        String Name = (String) table.getValueAt(selectedRow, table.getColumn("商品名称").getModelIndex());

        // 判断生产商名称是否包含退货状态标识
        if (!Name.contains("(未退货)") && !Name.contains("(已退货)")) {
            JOptionPane.showMessageDialog(null,"此顾客并没有退换货");
            return; // 如果商家名不包含退货状态，则退出方法
        }

        boolean isReplenished = Name.contains("(未退货)"); // 判断当前状态，用于更新状态值

        // 更新表格中的生产商列
        String newMerchant = isReplenished ? Name.replace("(未退货)", "(已退货)") : Name.replace("(已退货)", "(未退货)");
        DefaultTableModel model = (DefaultTableModel) table.getModel();
        model.setValueAt(newMerchant, selectedRow, table.getColumn("商品名称").getModelIndex());

        try {
            // 更新数据库中的生产商
            Connection conn = DBHelper.getConn();
            PreparedStatement ps = conn.prepareStatement("UPDATE corders SET name=? WHERE orderNum=?");
            ps.setString(1, Name);
            ps.setString(2, orderNum);

            int rowsAffected = ps.executeUpdate();

            if (rowsAffected > 0) {
                JOptionPane.showMessageDialog(null, "退货成功！！");
            } else {
                JOptionPane.showMessageDialog(null, "退货失败！！！");
            }

            ps.close();
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        //信息
        double balance = merchant.getMoney();
        double price  = Double.parseDouble(table.getValueAt(selectedRow, table.getColumn("商品价格").getModelIndex()).toString());
        String name = table.getValueAt(selectedRow, table.getColumn("商品名称").getModelIndex()).toString();
        String user = table.getValueAt(selectedRow, table.getColumn("用户").getModelIndex()).toString();

        //商家扣钱
        balance = balance - price;
        merchant.setMoney(balance);
        updateMerchant(merchant);

        //买家加钱
        CustomerService cs = new CustomerService();
        User customer = cs.findCname(user);
        double money = customer.getMoney();
        money = money + price;
        customer.setMoney(money);
        cs.updateCustomer(customer);

        //添加到订单里面
        LogService.addLogs(merchant,"商家 " + merchant.getName() + " 退货 " + name + "给买家 " + user);
    }



    //顾客换货
    public void ReplacementService(JTable table,User merchant){
        int selectedRow = table.getSelectedRow();
        if (selectedRow == -1) {
            JOptionPane.showMessageDialog(null, "请先选择一行商品！", "提示", JOptionPane.INFORMATION_MESSAGE);
            return;
        }

        // 获取选中行的信息
        String orderNum = (String) table.getValueAt(selectedRow, table.getColumn("订单编号").getModelIndex());
        String Name = (String) table.getValueAt(selectedRow, table.getColumn("商品名称").getModelIndex());
        String user = table.getValueAt(selectedRow, table.getColumn("用户").getModelIndex()).toString();
        String name = table.getValueAt(selectedRow, table.getColumn("商品名称").getModelIndex()).toString();

        // 判断生产商名称是否包含退货状态标识
        if (!Name.contains("(未换货)") && !Name.contains("(已换货)")) {
            JOptionPane.showMessageDialog(null,"此商品并不是换货商品，请重新选择！！！");
            return; // 如果商品名不包含换货状态，则退出方法
        }

        boolean isReplenished = Name.contains("(未换货)"); // 判断当前状态，用于更新状态值

        // 更新表格中的生产商列
        String newMerchant = isReplenished ? Name.replace("(未换货)", "(已换货)") : Name.replace("(已换货)", "(未换货)");
        DefaultTableModel model = (DefaultTableModel) table.getModel();
        model.setValueAt(newMerchant, selectedRow, table.getColumn("商品名称").getModelIndex());

        try {
            // 更新数据库中的生产商
            Connection conn = DBHelper.getConn();
            PreparedStatement ps = conn.prepareStatement("UPDATE corders SET name=? WHERE orderNum=?");
            ps.setString(1, newMerchant);
            ps.setString(2, orderNum);

            int rowsAffected = ps.executeUpdate();

            if (rowsAffected > 0) {
                JOptionPane.showMessageDialog(null, "换货成功！！");
            } else {
                JOptionPane.showMessageDialog(null, "换货失败！！！");
            }

            ps.close();
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }



        //扣除换货的商品
        updateShoeInDatabase(name,merchant);

        //添加到订单里面
        LogService.addLogs(merchant,"商家 " + merchant.getName() + " 换货 " + name + "给买家 " + user);
    }




    // 更新商品数量到商品-1
    public void updateShoeInDatabase(String name, User merchant) {
        Connection conn = DBHelper.getConn();
        PreparedStatement stmt = null;

        try {
            String sql = "UPDATE shoe SET scount = scount - 1 WHERE sname = ? AND smerchant = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, removeSuffix(name));
            stmt.setString(2, merchant.getName());

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 去除商品名称的后缀部分
    private String removeSuffix(String name) {
        int bracketIndex = name.indexOf("(未换货)");
        if (bracketIndex >= 0) {
            return name.substring(0, bracketIndex).trim();
        }
        return name;
    }







    //查询日志
    public void showLogData(JTable table, User merchant) {
        DefaultTableModel model = (DefaultTableModel) table.getModel();
        model.setRowCount(0);  // 清空表格

        try (Connection conn = DriverManager.getConnection(URL, DBUser, DBPassword);
             PreparedStatement ps = conn.prepareStatement("SELECT * FROM logs WHERE userName = ?");
        ) {

            ps.setString(1, merchant.getName());

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Object[] rowData = new Object[3];
                    rowData[0] = rs.getString("userName");
                    rowData[1] = rs.getString("times");
                    rowData[2] = rs.getString("logContext");

                    model.addRow(rowData);
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }



    // 刷新日志
    public JTable refreshLogData(JTable table, User merchant) {
        // 创建表头
        String[] columnNames = {"用户名", "时间", "日志内容"};

        // 设置表头
        DefaultTableModel tableModel = (DefaultTableModel) table.getModel();
        tableModel.setColumnIdentifiers(columnNames);

        // 查询数据库数据并添加到表格中
        Connection conn = DBHelper.getConn();
        String sql = "SELECT * FROM logs where userName = ?";
        PreparedStatement selectStat = null;
        ResultSet resultSet = null;

        try {
            selectStat = conn.prepareStatement(sql);
            selectStat.setString(1, merchant.getName());
            resultSet = selectStat.executeQuery();

            while (resultSet.next()) {
                Object[] rowData = new Object[3];
                rowData[0] = resultSet.getString("userName");
                rowData[1] = resultSet.getString("times");
                rowData[2] = resultSet.getString("logContext");

                // 添加数据行
                tableModel.addRow(rowData);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.closeAll(conn, selectStat, resultSet);
        }

        return table;
    }



    /*
     * 登出功能
     */
    public void Logout(User user_Login) {
        Date time = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss");
        String dataTimeNow = sdf.format(time);
        //记录登出系统时间
        LogService.addLogs(user_Login, user_Login.getName() + " 于" + dataTimeNow + "  登出系统。");
        JOptionPane.showMessageDialog(null, user_Login.getName() + " 商家于" + dataTimeNow + "  登出系统。");
        //将当前登出清除
        user_Login = null;
    }
}






